package com.lg.common.database

import android.content.ContentValues
import android.content.Context
import android.database.Cursor
import android.database.DatabaseUtils
import android.database.sqlite.SQLiteDatabase
import android.database.sqlite.SQLiteOpenHelper
import android.util.Log
import com.google.gson.Gson
import java.util.*


class DatabaseHelper(context: Context) : SQLiteOpenHelper(context, DATABASE_NAME, null, DATABASE_VERSION) {

    companion object {
        private const val DATABASE_VERSION = 7
        private const val DATABASE_NAME = "OfflineDatabase.db"
    }

    private val TAG = "DatabaseHelper"

    // Table Name
    private val TABLE_OFFLINE_DATA = "OFFLINE_DATA"
    private val TABLE_OFFLINE_CART_DATA = "OFFLINE_CART_DATA"
    private val TABLE_RECENTLY_VIEWED_PRODUCTS = "RECENTLY_VIEWED_PRODUCTS_DATA"
    private val TABLE_RECENT_SEARCH = "RECENT_SEARCH_DATA"

    // Column Names
    private val TABLE_OFFLINE_DATA_COLUMN_ID = "id"
    private val TABLE_OFFLINE_DATA_COLUMN_HASH_IDENTIFIER = "hashIdentifier"
    private val TABLE_OFFLINE_DATA_COLUMN_ETAG = "eTag"
    private val TABLE_OFFLINE_DATA_COLUMN_RESPONSE_DATA = "responsedata"

    private val TABLE_CART_DATA_COLUMN_ID = "id"
    private val TABLE_CART_DATA_COLUMN_PRODUCT_ID = "productId"
    private val TABLE_CART_DATA_COLUMN_QTY = "qty"
    private val TABLE_CART_DATA_COLUMN_PARAMS = "params"
    private val TABLE_CART_DATA_COLUMN_RELATED_PRODUCTS = "relatedProducts"

    private val TABLE_RECENTLY_VIEWED_PRODUCTS_COLUMN_ID = "id"
    private val TABLE_RECENTLY_VIEWED_PRODUCTS_COLUMN_STORE_ID = "storeId"
    private val TABLE_RECENTLY_VIEWED_PRODUCTS_COLUMN_CURRENCY_CODE = "currencyCode"
    private val TABLE_RECENTLY_VIEWED_PRODUCTS_COLUMN_PRODUCT_ID = "productId"
    private val TABLE_RECENTLY_VIEWED_PRODUCTS_COLUMN_PRODUCT_DATA = "productData"

    private val TABLE_RECENT_SEARCH_COLUMN_ID = "id"
    private val TABLE_RECENT_SEARCH_COLUMN_STORE_ID = "storeId"
    private val TABLE_RECENT_SEARCH_COLUMN_QUERY = "query"

    override fun onUpgrade(db: SQLiteDatabase?, oldVersion: Int, newVersion: Int) {
        // Drop older table if existed
        db?.execSQL("DROP TABLE IF EXISTS $TABLE_OFFLINE_DATA")
        db?.execSQL("DROP TABLE IF EXISTS $TABLE_OFFLINE_CART_DATA")
        db?.execSQL("DROP TABLE IF EXISTS $TABLE_RECENTLY_VIEWED_PRODUCTS")
        db?.execSQL("DROP TABLE IF EXISTS $TABLE_RECENT_SEARCH")

        // Create tables again
        onCreate(db)
    }

    override fun onCreate(db: SQLiteDatabase?) {
        val createOfflineDataTable = "CREATE TABLE $TABLE_OFFLINE_DATA (" +
                "$TABLE_OFFLINE_DATA_COLUMN_ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, " +
                "$TABLE_OFFLINE_DATA_COLUMN_HASH_IDENTIFIER VARCHAR, " +
                "$TABLE_OFFLINE_DATA_COLUMN_ETAG VARCHAR, " +
                "$TABLE_OFFLINE_DATA_COLUMN_RESPONSE_DATA VARCHAR)"
        db?.execSQL(createOfflineDataTable)

        val createOfflineCartDataTable = "CREATE TABLE " + TABLE_OFFLINE_CART_DATA + "(" +
                TABLE_CART_DATA_COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, " +
                TABLE_CART_DATA_COLUMN_PRODUCT_ID + " VARCHAR, " +
                TABLE_CART_DATA_COLUMN_QTY + " VARCHAR, " +
                TABLE_CART_DATA_COLUMN_PARAMS + " VARCHAR, " +
                TABLE_CART_DATA_COLUMN_RELATED_PRODUCTS + " VARCHAR) "
        db?.execSQL(createOfflineCartDataTable)

        val createRecentlyViewedProductsDataTable = "CREATE TABLE $TABLE_RECENTLY_VIEWED_PRODUCTS (" +
                "$TABLE_RECENTLY_VIEWED_PRODUCTS_COLUMN_ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, " +
                "$TABLE_RECENTLY_VIEWED_PRODUCTS_COLUMN_STORE_ID INTEGER, " +
                "$TABLE_RECENTLY_VIEWED_PRODUCTS_COLUMN_CURRENCY_CODE VARCHAR, " +
                "$TABLE_RECENTLY_VIEWED_PRODUCTS_COLUMN_PRODUCT_ID VARCHAR, " +
                "$TABLE_RECENTLY_VIEWED_PRODUCTS_COLUMN_PRODUCT_DATA VARCHAR)"
        db?.execSQL(createRecentlyViewedProductsDataTable)

        val createRecentSearchDataTable = "CREATE TABLE $TABLE_RECENT_SEARCH (" +
                "$TABLE_RECENT_SEARCH_COLUMN_ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, " +
                "$TABLE_RECENT_SEARCH_COLUMN_STORE_ID INTEGER, " +
                "$TABLE_RECENT_SEARCH_COLUMN_QUERY VARCHAR)"
        db?.execSQL(createRecentSearchDataTable)
    }


    /* Offline database related functions */

    fun addOrUpdateIntoOfflineTable(hashIdentifier: String, eTag: String, responseData: String) {
        val cursor = writableDatabase.rawQuery("SELECT $TABLE_OFFLINE_DATA_COLUMN_ETAG, $TABLE_OFFLINE_DATA_COLUMN_RESPONSE_DATA FROM $TABLE_OFFLINE_DATA WHERE $TABLE_OFFLINE_DATA_COLUMN_HASH_IDENTIFIER = '$hashIdentifier'", null)
        if (cursor.count == 0)
            insertIntoOfflineTable(hashIdentifier, eTag, responseData)
        else
            updateIntoOfflineTable(hashIdentifier, eTag, responseData)
        cursor.close()
    }

    private fun insertIntoOfflineTable(hashIdentifier: String, eTag: String, responseData: String) {
        // Create a new map of values, where column names are the keys
        val values = ContentValues()
        values.put(TABLE_OFFLINE_DATA_COLUMN_HASH_IDENTIFIER, hashIdentifier)
        values.put(TABLE_OFFLINE_DATA_COLUMN_ETAG, eTag)
        values.put(TABLE_OFFLINE_DATA_COLUMN_RESPONSE_DATA, responseData)

        // Insert the new row, returning the primary key value of the new row
        val newRowId = writableDatabase.insert(TABLE_OFFLINE_DATA, null, values)
    }

    private fun updateIntoOfflineTable(hashIdentifier: String, eTag: String, responseData: String) {
        // Create a new map of values, where column names are the keys
        val values = ContentValues()
        values.put(TABLE_OFFLINE_DATA_COLUMN_HASH_IDENTIFIER, hashIdentifier)
        values.put(TABLE_OFFLINE_DATA_COLUMN_ETAG, eTag)
        values.put(TABLE_OFFLINE_DATA_COLUMN_RESPONSE_DATA, responseData)

        // Define 'where' part of query.
        val selection = "$TABLE_OFFLINE_DATA_COLUMN_HASH_IDENTIFIER LIKE ?"

        // Specify arguments in placeholder order.
        val selectionArgs = arrayOf(hashIdentifier)

        // Issue SQL statement.
        writableDatabase.update(TABLE_OFFLINE_DATA, values, selection, selectionArgs)
    }


    fun clearRecentSearchData() {
        val db = this.writableDatabase
        db.execSQL("DELETE FROM $TABLE_RECENT_SEARCH")
    }
}